﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMSSyncData.Model.WMSModel;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.Oracle
{
    public class WMS_M_InStockDAL : BaseOracleDAL<WMS_T_InStock>
    {
        public List<WMS_T_InStock> GetSyncData(string billType)
        {
            var sql = @"SELECT rawtohex(sys_guid()) as GUID,temp.* FROM(
SELECT DISTINCT BillNO,RelatedbillNo as RelatedBill, billtype,0 AS status,creator as Creator,createtime as createtime,nvl(inwarehouse,' ') as Storage_SN,EXT1,EXT2,EXT3 FROM WMS_M_INSTOCK WHERE Data_Status=1 and billtype='" + billType + "')temp ";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        public List<WMS_T_InStock> GetSyncData()
        {
            var sql = @"SELECT rawtohex(sys_guid()) as GUID,temp.* FROM(
SELECT DISTINCT BillNO,RelatedbillNo as RelatedBill, billtype,0 AS status,creator as Creator,createtime as createtime,nvl(inwarehouse,' ') as Storage_SN,EXT1,EXT2,EXT3 FROM WMS_M_INSTOCK WHERE Data_Status=1 and (billtype='成品入库' or billtype='半成品入库' or billtype='手工成品入库'))temp ";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);

            return result;
        }

        public DataTable GetT()
        {
            var sql = @"SELECT BillNO,RelatedbillNo as RelatedBill, PartNumber,creator as Creator,createtime as createtime,inqty as Qty,nvl(inwarehouse,' ') as Storage_SN, unit
FROM WMS_M_INSTOCK WHERE Data_Status=1 and (billtype='成品入库' or billtype='半成品入库')";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            return dt;
        }

        public DataTable GetInventoryInfo(string billType)
        {
            var sql = @"SELECT BillNO,RelatedbillNo as RelatedBill, PartNumber,creator as Creator,createtime as createtime,inqty as Qty,nvl(inwarehouse,' ') as Storage_SN, unit,EXT1,EXT2
FROM WMS_M_INSTOCK WHERE Data_Status=1 and billtype='" + billType + "'";
            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];
            return dt;
        }

     

    }

    public class WMS_T_InStockRowsDAL : BaseOracleDAL<WMS_T_InStockRows>
    {
        public List<WMS_T_InStockRows> GetSyncData()
        {
            var sql = @"SELECT  rawtohex(sys_guid()) as GUID,BillNO,RowNO,partnumber,inqty as Planqty,unit FROM WMS_M_INSTOCK WHERE Data_Status=1 ";

            var dt = OracleDBHelper.GetDataSet(sql).Tables[0];

            var result = MakeTablePackage(dt);
            return result;
        }

        public int UpdateStatus(List<WMS_T_InStockRows> list)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (var item in list)
            {
                var sql = "UPDATE WMS_M_INSTOCK set  Data_Status=2,Data_ReadTime=SYSDATE where BillNo='" + item.BillNo + "' and rowNo='" + item.RowNo + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

        public int UpdateOutStatus(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("begin");
            foreach (DataRow item in dt.Rows)
            {
                var sql = "UPDATE wms_M_outStockRM set  Ext5='9' where BillNo='" + item["BillNo"].ToString() + "' and rowNo='" + item["RowNo"].ToString() + "';";
                sb.AppendLine(sql);
            }
            sb.AppendLine("end;");

            var resultSql = sb.ToString().Trim();
            var result = OracleDBHelper.ExecuteNonQuery(resultSql);
            return result;
        }

    }

}
